﻿using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using HuaCheng.Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Windows.Forms;

namespace HuaCheng.Controller
{
    public class ExcelControl
    {
        /// <summary>
        /// 读取EXCEL文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet GetDataSetByExcel(string filePath)
        {
            string fileType = System.IO.Path.GetExtension(filePath);           
            if (string.IsNullOrEmpty(fileType))
            {
                return null;
            }
            else
            {
                DataSet ds = new DataSet();
                //判断Excel文件是2003版本还是2007版本
                string connString = "";
                if (fileType == ".xls")
                {
                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                }
                else if(fileType == ".xlsx")
                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                //读取文件
                string sql_select = " SELECT * FROM [Sheet1$]";
                using (OleDbConnection conn = new OleDbConnection(connString))
                try
                {
                    using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn))
                    {
                        conn.Open();
                        cmd.Fill(ds);
                    }
                }
                catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); }
                if (ds == null || ds.Tables.Count <= 0) return null;
                return ds;
            }
        }
        /// <summary>
        /// 将斜坡工况DataSet中的内容存入List
        /// </summary>
        /// <param name="openFileName"></param>
        /// <returns></returns>
        public static List<SlopeStep> GetSlopeList(string openFileName)
        {
            List<SlopeStep> slopeSet = new List<SlopeStep>();
            try
            {
                DataSet ds = GetDataSetByExcel(openFileName);
                int i = 0;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    SlopeStep slopeStep = new SlopeStep();
                    slopeStep.SerialNum = Convert.ToString(++i);
                    slopeStep.CurrAmp1 = Convert.ToString(Math.Round(Convert.ToSingle(dr[ds.Tables[0].Columns[0]]), 4));
                    slopeStep.Slope = Convert.ToString(Math.Round(Convert.ToSingle(dr[ds.Tables[0].Columns[1]]), 4));
                    slopeStep.CurrAmp2 = Convert.ToString(Math.Round(Convert.ToSingle(dr[ds.Tables[0].Columns[2]]), 4));
                    slopeStep.Time = Convert.ToString(dr[ds.Tables[0].Columns[3]]);
                    slopeSet.Add(slopeStep);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace + ex.Message);
                MessageBox.Show("导入工况文件格式不正确！");
            }
            return slopeSet;
        }
        /// <summary>
        /// 将脉冲工况DataSet中的内容存入List
        /// </summary>
        /// <param name="openFileName"></param>
        /// <returns></returns>
        public static List<PulseStep> GetPulseList(string openFileName)
        {
            List<PulseStep> pulseSet = new List<PulseStep>();
            try
            {
                DataSet ds = GetDataSetByExcel(openFileName);
                int i = 0;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    PulseStep pulseStep = new PulseStep();
                    pulseStep.SerialNum = Convert.ToString(++i);
                    pulseStep.CurrValue = Convert.ToString(Math.Round(Convert.ToSingle(dr[ds.Tables[0].Columns[0]]), 4));
                    pulseStep.Time = Convert.ToString(dr[ds.Tables[0].Columns[1]]);
                    pulseSet.Add(pulseStep);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.StackTrace + ex.Message);
                MessageBox.Show("导入工况文件格式不正确！");
            }
            return pulseSet;
        }
        /// <summary>
        /// 将校正因子DataSet中的内容存入List
        /// </summary>
        /// <param name="openFileName"></param>
        /// <returns></returns>
        public static List<CorrectionFactor> GetCorrectionFactorList(string openFileName)
        {
            List<CorrectionFactor> correctionFactorList = new List<CorrectionFactor>();
            DataSet ds = GetDataSetByExcel(openFileName);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                CorrectionFactor correctionFactor = new CorrectionFactor();
                correctionFactor.Max = Convert.ToString(dr[ds.Tables[0].Columns[0]]);
                correctionFactor.Min = Convert.ToString(dr[ds.Tables[0].Columns[1]]);
                correctionFactor.ControlK = Convert.ToString(dr[ds.Tables[0].Columns[2]]);
                correctionFactor.ControlB = Convert.ToString(dr[ds.Tables[0].Columns[3]]);
                correctionFactor.ShowK = Convert.ToString(dr[ds.Tables[0].Columns[4]]);
                correctionFactor.ShowB = Convert.ToString(dr[ds.Tables[0].Columns[5]]);
                correctionFactorList.Add(correctionFactor);
            }
            return correctionFactorList;
        }
        /// <summary>
        /// 将slopeList存入到excel文件中
        /// </summary>
        /// <param name="slopeList"></param>
        /// <param name="filePath"></param>
        public static void SetSlopeDataToExcel(List<SlopeStep> slopeList, string filePath)
        {
            //创建工作簿对象
            IWorkbook workbook;
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType))
            {
                //如果用户没有选择保存的格式，则默认使用2003版本的XLS格式
                filePath = filePath + ".xls";
                workbook = new HSSFWorkbook();
            }
            else
            {
                //XSSFWorkbook 适用2007版本的XLSX格式，HSSFWorkbook 适用2003版本的XLS格式
                if (fileType == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileType == ".xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }
            }
            //创建工作表
            //必须设置成Sheet1,，与SELECT * FROM [Sheet1$]保持一致，不然GetDataSetByExcel()方法无法读取excel文件
            ISheet sheet = workbook.CreateSheet("Sheet1");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("电流幅值1(A)");
            row0.CreateCell(1).SetCellValue("斜率");
            row0.CreateCell(2).SetCellValue("电流幅值2(A)");
            row0.CreateCell(3).SetCellValue("时间（s）");
            for (int r = 1; r <= slopeList.Count; r++)
            {
                IRow row = sheet.CreateRow(r);
                row.CreateCell(0).SetCellValue(slopeList[r - 1].CurrAmp1);
                row.CreateCell(1).SetCellValue(slopeList[r - 1].Slope);
                row.CreateCell(2).SetCellValue(slopeList[r - 1].CurrAmp2);
                row.CreateCell(3).SetCellValue(slopeList[r - 1].Time);
            }
            using (FileStream url = new FileStream(filePath, FileMode.Create))
            {
                workbook.Write(url);
            }
        }
        /// <summary>
        /// 将pulseList存入到excel文件中
        /// </summary>
        /// <param name="pulseList"></param>
        /// <param name="filePath"></param>
        public static void SetPulseDataToExcel(List<PulseStep> pulseList, string filePath)
        {
            //创建工作簿对象
            IWorkbook workbook;
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType))
            {
                //如果用户没有选择保存的格式，则默认使用2003版本的XLS格式
                filePath = filePath + ".xls";
                workbook = new HSSFWorkbook();
            }
            else
            {
                //XSSFWorkbook 适用2007版本的XLSX格式，HSSFWorkbook 适用2003版本的XLS格式
                if (fileType == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                }
                else if (fileType == ".xls")
                {
                    workbook = new HSSFWorkbook();
                }
                else
                {
                    workbook = null;
                }
            }
            //创建工作表
            //必须设置成Sheet1,，与SELECT * FROM [Sheet1$]保持一致，不然GetDataSetByExcel()方法无法读取excel文件
            ISheet sheet = workbook.CreateSheet("Sheet1");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("电流（A)");
            row0.CreateCell(1).SetCellValue("时间（s）");
            for (int r = 1; r <= pulseList.Count; r++)
            {
                IRow row = sheet.CreateRow(r);
                row.CreateCell(0).SetCellValue(pulseList[r - 1].CurrValue);
                row.CreateCell(1).SetCellValue(pulseList[r - 1].Time);
            }
            using (FileStream url = new FileStream(filePath, FileMode.Create))
            {
                workbook.Write(url);
            }
        }
    }
}
